Present invention embodiments are related to a method, at least one processing device, and a computer program product for identifying rows to be processed for a relational database query, in a parallel system, wherein the rows have a unique grouping key within a parallel element such that partial aggregation of the rows having the unique grouping key can be avoided on the parallel element and groups that are identified as only existing within a single respective parallel element need not be communicated to other parallel elements for further aggregation. In particular, only those rows having duplicate grouping keys that exist on multiple parallel elements are communicated to other parallel elements for further aggregation.
Group-by in a parallel system involves coordination among parallel elements to combine local aggregates for a group into a final global aggregate. Various approaches may be employed to perform this operation.
In a first technique, all parallel elements access a single aggregation data structure, which may be a hash table or sort-based. Because there is only one copy, memory requirements are reduced. However, synchronization cost is high. Improvements to this method include synchronizing using atomics instead of mutexes, treating hot data differently from cold data, etc.
In a second technique, data may be partitioned, by range or via a hash, based on a group-by key. Each partition is distributed to a different parallel element for grouping and aggregation. A cost to partition the data is high, particularly when communicating the data among parallel elements is expensive.
In a third technique, the data is distributed in a round robin fashion to each parallel element such that each parallel element does a partial aggregation on a subset of the data. All partial results from each of the parallel elements are merged to generate final aggregation results. A cost for communicating and merging the results is high.
Using a fourth technique, a hierarchy of aggregators is provided. The aggregators are loosely aligned to a memory hierarchy. Tuples are aggregated in a highest level aggregator as far as possible, and only when the highest level aggregator runs out of memory, tuples are migrated to lower level aggregators. Among the high level aggregators, merge cost and memory consumption can be high.